A data warehouse may be a database used for decision support activities (e.g., reporting and data analysis). The data warehouse may be a central repository created by integrating and persisting (e.g., saving changes over time) historical data from multiple data sources. One of the most often used relational structures for persisting data warehouse data is a “star schema”. The entities of a star schema are typically divided into two categories. The two categories typically include facts and dimensions. Facts may persist measures of a business activity (e.g., cost incurred by use of vendor services) while dimensions persist reference information about a measurable event (e.g., date/time of the incurred cost, vendor: set of pertinent attributes, client: set of pertinent attributes, and/or the like). The historical aspect of data within a data warehouse is central to the persistence implementation. History may be tracked within the fact tables (e.g., incurred cost is being persisted daily, weekly, bi-weekly, monthly, quarterly and/or the like) and within the dimensions as the dimensional attributes change with time (ex: vendor risk factor).
The changing dimensions are often called slowly changing dimension (SCD) because the changes within the dimensions are infrequent and irregular. The historical persistence aspect of dimensions is accomplished by different types of SCDs. The types of SCDs may include (1) Type 0—no action; (2) Type 1—attributes are overwritten as changes occur—no new records are created; (3) Type 2—changes of attributes cause new records to be persisted; (4) Type 3—new columns are created for attributes for which current and previous historical values are being persisted; (5) Type 4—data is persisted in two dimensional tables—dimensional table with current attribute set and history table; and (6) Type 6—combination of Type 1, Type 2, and Type 3.
For accurate data analysis (e.g., trending, data mining), relating coherently dimensional historical information to fact historical data is necessary for the successful implementation of the data warehouse. Typically, the 2-fold data access required in many data warehouse use cases is to relate fact data in particular time interval to historically correct for the time interval value of some dimensional attribute and/or current (latest known) value of another or the same attribute. Currently, there is no known solution for accurate persistence and efficient access of slowly changing dimensions (SCD) type 6 when utilizing a star join.